﻿using System.Text;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using InvestmentIntelligence.Logging.Core;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class TimeSerieRepository : ISqlTimeSerieRepository
    {
        //todo: remove this
        #region temporary

        public static readonly Dictionary<string, TimeSpan> Times = new Dictionary<string, TimeSpan>()
                                                            {
                                                                {"RANGE", new TimeSpan()},
                                                                {"BACKWARD", new TimeSpan()},
                                                                {"FORWARD", new TimeSpan()},
                                                            };
        public static readonly Dictionary<string, int> Counts = new Dictionary<string, int>()
                                                            {
                                                                {"RANGE", 0},
                                                                {"BACKWARD", 0},
                                                                {"FORWARD", 0},
                                                            };
        private static readonly object Lock = new object();

        public static void LogStats()
        {
            lock (Lock)
            {
                Log.Info("Time series reading stats:");
                foreach (var kv in Times)
                {
                    Log.Info(kv.Key + " : " + Counts[kv.Key] + " reads in " + kv.Value);
                }
            }
        }

        #endregion

        public List<TimeSerieAdbInfo> GetAdbInfos(int securityId, DateTime? startDate = null, DateTime? endDate = null, TimeSerieAdbSearchOptions options = new TimeSerieAdbSearchOptions())
        {
            var timer = new Stopwatch();
            timer.Start();
            var sql = string.Format(@"
                            SELECT 
                                [Date], [Close], [MarketCap], [StockReturn]
                            FROM 
                                MarketData.TimeSerie
                            WHERE 
                                SecurityId = @sId
                                {0}                                
                            ORDER BY [Date]
                            ", BuildSearchFilter(options, startDate, endDate, true));

            using (var dc = new DataConnection())
            {
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@StartDate", startDate);
                dc.Parameters.AddWithValue("@EndDate", endDate);
                var data = dc.Query<TimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["RANGE"] += timer.Elapsed;
                    Counts["RANGE"] ++;
                }

                return data;
            }
        }

        public Dictionary<int, List<TimeSerieAdbInfo>> LoadByOriginalPositions(int fundBookEntityId)
        {
            var sql = string.Format(@"
SELECT ts.[SecurityId], ts.[Date], ts.[Close], ts.[MarketCap], ts.[StockReturn]
FROM
(SELECT SecurityId, DATEADD(DAY, -10, MIN([Date])) MinDate, DATEADD(DAY, 60, MAX([Date])) MaxDate
  FROM [TradingData].[OriginalPosition] op
  LEFT JOIN [MarketData].[Security] s
  ON op.SecurityId = s.Id
  LEFT JOIN [MarketData].[SecurityClass] sc
  ON sc.Id = s.ClassId
  WHERE FundBookEntityId = {0} AND sc.Include = 1
  GROUP BY SecurityId) dates
INNER JOIN MarketData.TimeSerie ts
ON ts.SecurityId = dates.SecurityId and ts.Date >= MinDate AND ts.Date <= MaxDate
                            ", fundBookEntityId);

            using (var dc = new DataConnection())
            {
                var data = dc.Query<TimeSerieAdbInfoWithSecurityId>(sql).ToList();
                return data.GroupBy(i => i.SecurityId).ToDictionary(g => g.Key,
                    g => g.OfType<TimeSerieAdbInfo>().ToList());
            }
        }
        
        public IEnumerable<TimeSerieAdbInfo> EnumerateBackward(int securityId, DateTime date)
        {
            var timer = new Stopwatch();
            timer.Start();
            var sql = @"
                            SELECT 
                                [Date], [Close], [MarketCap], [StockReturn]
                            FROM 
                                MarketData.TimeSerie
                            WHERE 
                                SecurityId = @sId
                                AND [Date] < @Date
                            ORDER BY [Date] DESC";

            using (var dc = new DataConnection())
            {
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@Date", date);
                var data = dc.Query<TimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["BACKWARD"] += timer.Elapsed;
                    Counts["BACKWARD"]++;
                }
                return data;
            }
        }

        public IEnumerable<TimeSerieAdbInfo> EnumerateForward(int securityId, DateTime date)
        {
            var timer = new Stopwatch();
            timer.Start();
            var sql = @"
                            SELECT 
                                [Date], [Close], [MarketCap], [StockReturn]
                            FROM 
                                MarketData.TimeSerie
                            WHERE 
                                SecurityId = @sId
                                AND [Date] > @Date
                            ORDER BY [Date]";

            using (var dc = new DataConnection())
            {
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@Date", date);
                var data = dc.Query<TimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["FORWARD"] += timer.Elapsed;
                    Counts["FORWARD"]++;
                }
                return data;
            }
        }

//        public TimeSerieAdbInfo GetPrevAdbInfo(int securityId, DateTime date, TimeSerieAdbSearchOptions options)
//        {
//            var sql = string.Format(@"
//                            SELECT TOP 1
//                                [Date], [Close], [MarketCap], [StockReturn]
//                            FROM 
//                                MarketData.TimeSerie
//                            WHERE 
//                                SecurityId = @sId
//                                {0}
//                                AND [Date] < @Date
//                            ORDER BY [Date] DESC
//                            ", BuildSearchFilter(options, null, date, false));

//            using (var dc = new DataConnection())
//            {
//                dc.Parameters.AddWithValue("@sId", securityId);
//                dc.Parameters.AddWithValue("@EndDate", date);
//                return dc.QuerySingle<TimeSerieAdbInfo>(sql);
//            }
//        }

//        public TimeSerieAdbInfo GetNextAdbInfo(int securityId, DateTime date, TimeSerieAdbSearchOptions options)
//        {
//            var sql = string.Format(@"
//                            SELECT TOP 1
//                                [Date], [Close], [MarketCap], [StockReturn]
//                            FROM 
//                                MarketData.TimeSerie
//                            WHERE 
//                                SecurityId = @sId
//                                {0}
//                            ORDER BY [Date]
//                            ", BuildSearchFilter(options, date, null, false));

//            using (var dc = new DataConnection())
//            {
//                dc.Parameters.AddWithValue("@sId", securityId);
//                dc.Parameters.AddWithValue("@StartDate", date);
//                return dc.QuerySingle<TimeSerieAdbInfo>(sql);
//            }
//        }

        private static string BuildSearchFilter(TimeSerieAdbSearchOptions options, DateTime? startDate, DateTime? endDate, bool includeDates)
        {
            var sOptions = string.Empty;
            if (options.ClosePriceRequired) sOptions += " AND [Close] IS NOT NULL AND [Close] <> 0 ";
            if (options.MarketCapRequired) sOptions += " AND [MarketCap] IS NOT NULL AND [MarketCap] <> 0 ";
            if (options.StockReturnRequired) sOptions += " AND [StockReturn] IS NOT NULL ";
            if (startDate.HasValue)
            {
                sOptions += includeDates
                    ? " AND [Date] >= @StartDate"
                    : " AND [Date] > @StartDate";
            }
            if (endDate.HasValue)
            {
                sOptions += includeDates
                    ? " AND [Date] <= @EndDate"
                    : " AND [Date] < @EndDate";
            }

            return sOptions;
        }

        public List<TraceReturnAdbInfo> GetReturns(int securityId, DateTime eventDate, int priorWindow, int postWindow)
        {
            using (var dc = new DataConnection())
            {
                const string cmd = @"
SELECT a.[Date], a.[Return]
FROM
(
	SELECT TOP {0} [Date], StockReturn AS 'Return' 
	FROM MarketData.TimeSerie
	WHERE [Date] < @EventDate AND SecurityId = @SecurityId	
    ORDER BY [Date] DESC
) a

UNION

SELECT b.[Date], b.[Return]
FROM
(
	SELECT TOP {1} [Date], StockReturn AS 'Return'
	FROM MarketData.TimeSerie
	WHERE [Date] > @EventDate AND SecurityId = @SecurityId
    ORDER BY [Date] ASC
) b";

                dc.Parameters.AddWithValue("@SecurityId", securityId);
                dc.Parameters.AddWithValue("@EventDate", eventDate);
                var sqlQuery = string.Format(cmd, priorWindow, postWindow);

                return dc.Query<TraceReturnAdbInfo>(sqlQuery).OrderBy(item => item.Date).ToList();
            }
        }

//        public ICollection<SeriesRange<TimeSerieAdbInfo>> LoadRanges(IDictionary<int, IList<Tuple<DateTime, DateTime?>>> dateRanges)
//        {
//            using (var dc = new DataConnection())
//            {
//                // bulk insert ranges into temp table
//                var tempTableName = CreateTempRangesTable(dc);

//                var rangeIdCounter = 0;
//                var rows = from securityRanges in dateRanges
//                    from range in securityRanges.Value
//                    select new
//                           {
//                               RangeId = rangeIdCounter++,
//                               SecurityId = securityRanges.Key,
//                               StartDate = range.Item1,
//                               EndDate = range.Item2
//                           };

//                dc.BulkCopy(tempTableName, rows);

//                // join tables and get result

//                var sql = string.Format(@"
//SELECT t.*, ts.Date, ts.[Close], ts.StockReturn, ts.MarketCap
//FROM {0} t
//INNER JOIN [MarketData].[TimeSerie] ts
//ON t.SecurityId = ts.SecurityId AND t.StartDate <= ts.Date AND (t.EndDate IS NULL OR ts.Date <= t.EndDate)
//", tempTableName);


//                var resultRows = dc.Query<TimeSerieAdbInfoWithRange>(sql).ToList();

//                // map result to objects

//                var resultRanges = resultRows.GroupBy(r => new {r.RangeId, r.SecurityId, r.StartDate, r.EndDate})
//                    .Select(g => new SeriesRange<TimeSerieAdbInfo>
//                                 {
//                                     SerieId = g.Key.SecurityId,
//                                     StartDate = g.Key.StartDate,
//                                     EndDate = g.Key.EndDate,
//                                     Series = g.OfType<TimeSerieAdbInfo>().ToList()
//                                 });
//                return resultRanges.ToList();
//            }

//        }

        //private class TimeSerieAdbInfoWithRange: TimeSerieAdbInfo
        //{
        //    public int RangeId { set; get; }
        //    public int SecurityId { set; get; }
        //    public DateTime StartDate { set; get; }
        //    public DateTime? EndDate { set; get; }
        //}

        private class TimeSerieAdbInfoWithSecurityId : TimeSerieAdbInfo
        {
            public int SecurityId { set; get; }
        }

//        private string CreateTempRangesTable(DataConnection dc)
//        {
//            var tableName = "##TimeSerieRanges" + Guid.NewGuid().ToString().Replace("-", "");
//            var createTableSql = string.Format(@"
//CREATE TABLE {0}(
//RangeId INT NOT NULL,
//SecurityId NOT NULL,
//StartDate DATETIME NOT NULL,
//EndDate DATETIME NULL
//)
//", tableName);
//            dc.Execute(createTableSql, CommandType.Text);
//            return tableName;
//        }

    }
}
